  package com.zz.repository;


import java.math.BigDecimal;
import java.util.List;


import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.zz.entity.Product;

public interface ProductRepository extends JpaRepository<Product,String>{
	//显示所有商品信息并进行分页
	@Query(value="select p.id,p.name,p.type,p.location,p.num,p.price,s.name as sup,w.name as wname from product p left join supplier s on (p.supplier_id=s.id) left join ware w on (p.ware_id=w.id)",
			countQuery="select count(*) from product",
			nativeQuery=true)
	public Page<Object[]> getall(Pageable pageable);
	
	//根据id修改商品信息
	@Transactional
    @Modifying
	@Query(value="update product p left join supplier s on (p.supplier_id=s.id) left join ware w on (p.ware_id=w.id) set p.type=?1,p.location=?2,p.num=?3,p.ware_id=?4 where p.id=?5",nativeQuery=true)
	public Integer updatepdt(String type,String location,Integer num,String wid,String id);
	
	//根据id删除商品
	@Query(value="delete from product where id=?1",nativeQuery=true)
	public int deletepdt(String id);
	
	//根据名字查询商品信息
	@Query(value="select p.id,p.name,p.type,p.location,p.num,p.price,s.name as sup,w.name as wname from product p left join supplier s on (p.supplier_id=s.id) left join ware w on (p.ware_id=w.id) where p.name=?1",
			countQuery="select count(*) from product where name=?1",
			nativeQuery=true)
	public Page<Object[]> findByName(String name,Pageable pageable);
	
	
	@Query(value="select * from product where name=?1 and type=?2 and ware_id=?3 and supplier_id=?4",nativeQuery=true)
	public Product findByNameAndTypeAndWareIdAndSupplierId(String name,String type,String wareid,String suppid);
	@Query(value="select pr.id,pr.name,pr.type,pr.price,pr.num,pr.location,s.name as gys,w.name as ck from product pr left join supplier s on (pr.supplier_id=s.id) left join ware w on (pr.ware_id=w.id) where pr.name=?1",nativeQuery = true)
	public List<Object[]> selectproductRKlistByname(String name);

	
	@Query(value="select pr.id,pr.name,s.name as gys,w.name as ck from product pr left join supplier s on (pr.supplier_id=s.id) left join ware w on (pr.ware_id=w.id) where pr.name=?1 and w.name=?2",nativeQuery = true)
	public List<Object[]> selectproductlistByPrnameandWarename(String productname,String warename);
	@Query(value="select pr.id,pr.name from product pr left join supplier s on (pr.supplier_id=s.id) left join ware w on (pr.ware_id=w.id) where pr.name=?1 and w.name=?2 and s.name=?3",nativeQuery = true)
	public List<Object[]> findprofuctIdByPWS(String productname,String warename,String suppliername);

}


